package dao;

import gui.PublicMethod;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

import model.CommonList;
import model.Meter;
import model.Recharge;

public class DaoImp_Recharge implements IDao_Recharge
{

	private DaoMethod method;
	private Connection conn;
	private CommonList common;

	public DaoImp_Recharge()
	{
		conn = DaoMethod.getConnection();
		method = new DaoMethod();
		common = CommonList.getInstance();
	}

	public List<Recharge> queryAll(int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Recharge> queryByName(String name, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where name='"
				+ name + "' and isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where name='"
					+ name + "' and isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where name='"
				+ name + "' order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Recharge> queryByDate(Date start, Date end)
	{
		String exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where date_recharge>='"
				+ start
				+ "' and date_recharge<='"
				+ end
				+ "' order by date_recharge desc";
		return getMeterList(exe);
	}

	public List<Recharge> queryByTelephone(String telephone, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where telephone='"
				+ telephone + "' and isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where telephone='"
					+ telephone + "' and isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where telephone='"
				+ telephone + "' order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Recharge> queryByDate(String date_recharge, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where date_recharge='"
				+ date_recharge + "' and isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where date_recharge='"
					+ date_recharge + "' and isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where date_recharge='"
				+ date_recharge + "' order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Recharge> queryByMeter(int meter_recharge, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where meter_recharge='"
				+ meter_recharge + "' and isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where meter_recharge='"
					+ meter_recharge + "' and isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where meter_recharge='"
				+ meter_recharge + "' order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	public List<Recharge> queryByRent(int rent_recharge, int scale)
	{
		String exe = null;
		switch (scale)
		{
		case PublicMethod.in:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where rent_recharge='"
				+ rent_recharge + "' and isout=0 order by date_recharge desc";
			break;
		case PublicMethod.out:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where rent_recharge='"
					+ rent_recharge + "' and isout=1 order by date_recharge desc";
			break;
		case PublicMethod.both:
			exe = "select id,name,date_recharge,meter_recharge,rent_recharge,telephone from recharge_view where rent_recharge='"
				+ rent_recharge + "' order by date_recharge desc";
			break;
		default:
			break;
		}
		return getMeterList(exe);
	}

	private List<Recharge> getMeterList(String exe)
	{
		Statement st = null;
		ResultSet rs = null;
		List<Recharge> list = new ArrayList<Recharge>();
		try
		{
			st = conn.createStatement();
			rs = st.executeQuery(exe);
			if (rs.next())
			{
				do
				{
					Recharge recharge = new Recharge();
					recharge.setId(rs.getInt(1));
					recharge.setName(rs.getString(2));
					recharge.setDate_recharge(rs.getDate(3));
					recharge.setMeterRecharge(rs.getInt(4));
					recharge.setRentRecharge(rs.getInt(5));
					recharge.setTelephone(rs.getString(6));
					list.add(recharge);
				}
				while (rs.next());
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return list;
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
				}
			}
			catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			try
			{
				if (st != null)
				{
					st.close();
				}
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return list;
	}

	public int addRecharge(Recharge recharge)
	{
		String exe = "insert into recharge(student_id,date_recharge,meter_recharge,rent_recharge)values(?,?,?,?)";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getStudentName().get(recharge.getName()));
		param.add(recharge.getDate_recharge());
		param.add(recharge.getMeterRecharge());
		param.add(recharge.getRentRecharge());
		return method.pstUpdate(exe, param);
	}

	public int deteleRecharge(Recharge currentRecharge)
	{
		String exe = "delete from recharge where student_id=? and date_recharge=? and meter_recharge=? and rent_recharge=?";
		ArrayList<Object> param = new ArrayList<Object>();
		param.add(common.getStudentName().get(currentRecharge.getName()));
		param.add(currentRecharge.getDate_recharge());
		param.add(currentRecharge.getMeterRecharge());
		param.add(currentRecharge.getRentRecharge());
		return method.pstUpdate(exe, param);
	}
}
